/*
 * 基于ESP32S3+SPIFFS+AsyncWebServer+SQLite3的硬件地址归属品牌(厂商)查询工具
 * 作者：HonestQiao(honestqiao@163.com)
 * 日期：2023-08-03
 * 
 * 说明：通过硬件设备的Mac地址，查询其所对应的品牌或者厂商信息
 * 
 * 参考资料:
 * ESP32 简单的WEB Server和GET参数读取示例
 * https://lingshunlab.com/book/esp32/esp32-web-server-and-read-get-parameters
 * 
 * me-no-dev/ESPAsyncWebServer
 * https://github.com/me-no-dev/ESPAsyncWebServer#specifying-cache-control-header
 * 
 * ESP32 Web Server using SPIFFS (SPI Flash File System)
 * https://randomnerdtutorials.com/esp32-web-server-spiffs-spi-flash-file-system/
 * 
 * 
 * esp32_arduino_sqlite3_lib
 * https://github.com/siara-cc/esp32_arduino_sqlite3_lib/tree/master
 * 
 * bootstrap
 * https://code.z01.com/bootstrap/examples/search/
 * 
 * 硬件地址数据集：
 * https://regauth.standards.ieee.org/standards-ra-web/pub/view.html
 */


#include <WiFi.h>
#include <WiFiClient.h>
#include <AsyncTCP.h>
#include <ESPAsyncWebSrv.h>
#include <ESPmDNS.h>
#include <sqlite3.h>
#include <SPI.h>
#include <FS.h>
#include "SPIFFS.h"

const char *ssid = "********";
const char *password = "********";

AsyncWebServer server(80);

const int led = 21;
String html_result = "";

sqlite3 *db1;
int rc;
sqlite3_stmt *res;
int rec_count = 0;
const char *tail;

void notFound(AsyncWebServerRequest *request) {
  request->send(404, "text/plain", "Not found");
}

int openDb(const char *filename, sqlite3 **db) {
  int rc = sqlite3_open(filename, db);
  if (rc) {
    Serial.printf("Can't open database: %s\n", sqlite3_errmsg(*db));
    return rc;
  } else {
    Serial.printf("Opened database successfully\n");
  }
  return rc;
}

void setup ( void ) {
  pinMode(led, OUTPUT);
  digitalWrite(led, 0);
  Serial.begin(115200);
  WiFi.mode(WIFI_STA);
  WiFi.begin(ssid, password);
  Serial.println("");

  // Wait for connection
  while ( WiFi.status() != WL_CONNECTED ) {
    delay ( 500 );
    Serial.print ( "." );
  }

  Serial.println ( "" );
  Serial.print ( "Connected to " );
  Serial.println ( ssid );
  Serial.print ( "IP address: " );
  Serial.println ( WiFi.localIP() );

  if ( MDNS.begin ( "esp32" ) ) {
    Serial.println ( "MDNS responder started" );
  }

  if (!SPIFFS.begin(true)) {
    Serial.println("Failed to mount file system");
    return;
  }

  Serial.println("Read result.html from file system");
  File file = SPIFFS.open("/www/index.html", "r");
  if (!file) {
    Serial.println("Failed to open file for reading");
    return;
  }

  while (file.available()) {
    html_result += char(file.read());
  }
  file.close();

  Serial.print("Length of result.html: ");
  Serial.println(html_result.length());

  sqlite3_initialize();

  // Open database
  if (openDb("/spiffs/oui.db", &db1))
    return;

  server.serveStatic("/bootstrap.min.css", SPIFFS, "/www/bootstrap.min.css").setCacheControl("max-age=86400");
  server.serveStatic("/bootstrap.min.css.map", SPIFFS, "/www/bootstrap.min.css.map").setCacheControl("max-age=86400");

  server.on("/", HTTP_GET, [](AsyncWebServerRequest * request) {
    String html_result_resp = html_result;
    html_result_resp.replace("{RESULT}", "<br/><br/>");
    request->send(200, "text/html", html_result_resp);
  });

  server.on("/query_db", HTTP_GET, [](AsyncWebServerRequest * request) {
    digitalWrite ( led, 1 );
    String inMacAddress = "";
    if (request->hasParam("mac")) {
      inMacAddress = request->getParam("mac")->value();
      Serial.print("query for ");
      Serial.println(inMacAddress);
    } else {
      String resp = "query is empty: ";
      String html_result_resp = html_result;
      html_result_resp.replace("{RESULT}", resp);
      request->send(200, "text/html", html_result_resp);
      Serial.println(resp.c_str());
      digitalWrite ( led, 0 );
      return;
    }

    String sAssignment = inMacAddress;
    for (int i = 0; i < sAssignment.length() - 1; ++i) {
      char c = sAssignment.charAt(i);
      if (c == ':' || c == '-') {
        sAssignment.remove(i, 1);
      }
    }

    if (sAssignment.length() < 6) {
      String resp = "query is less than 6 valid chars: ";
      String html_result_resp = html_result;
      html_result_resp.replace("{RESULT}", resp);
      request->send(200, "text/html", html_result_resp);
      Serial.println(resp.c_str());
      digitalWrite ( led, 0 );
      return;
    }
    sAssignment = sAssignment.substring(0, 6);
    sAssignment.toUpperCase();

    String sql = "Select count(*) from oui where Assignment = '";
    sql += sAssignment;
    sql += "'";
    Serial.print("SQL: ");
    Serial.println(sql.c_str());
    rc = sqlite3_prepare_v2(db1, sql.c_str(), -1, &res, &tail);
    if (rc != SQLITE_OK) {
      String resp = "Failed to fetch data: ";
      resp += sqlite3_errmsg(db1);
      String html_result_resp = html_result;
      html_result_resp.replace("{RESULT}", resp);
      request->send(200, "text/html", html_result_resp);
      Serial.println(resp.c_str());
      digitalWrite ( led, 0 );
      return;
    }
    while (sqlite3_step(res) == SQLITE_ROW) {
      rec_count = sqlite3_column_int(res, 0);
      if (rec_count > 50) {
        String resp = "Too many records: ";
        resp += rec_count;
        String html_result_resp = html_result;
        html_result_resp.replace("{RESULT}", resp);
        request->send(200, "text/html", html_result_resp);
        Serial.println(resp.c_str());
        sqlite3_finalize(res);

        digitalWrite ( led, 0 );
        return;
      }
    }
    sqlite3_finalize(res);

    sql = "Select Assignment,OrganizationName from oui where Assignment = '";
    sql += sAssignment;
    sql += "'";
    rc = sqlite3_prepare_v2(db1, sql.c_str(), -1, &res, &tail);
    if (rc != SQLITE_OK) {
      String resp = "Failed to fetch data: ";
      resp += sqlite3_errmsg(db1);
      String html_result_resp = html_result;
      html_result_resp.replace("{RESULT}", resp);
      request->send(200, "text/html", html_result_resp);
      Serial.println(resp.c_str());
      digitalWrite ( led, 0 );
      return;
    }

    rec_count = 0;
    String resp = "<table cellspacing='1' cellpadding='1' border='1'><tr><td>Mac Address</td><td>Assignment</td><td>Organization Name</td></tr>";
    while (sqlite3_step(res) == SQLITE_ROW) {
      resp += "<tr><td>";
      resp += inMacAddress;
      resp += "</td><td>";
      resp += (const char *) sqlite3_column_text(res, 0);
      resp += "</td><td>";
      resp += (const char *) sqlite3_column_text(res, 1);
      resp += "</td></tr>";
      rec_count++;
    }
    resp += "</table><br><br>Number of records: ";
    resp += rec_count;

    String html_result_resp = html_result;
    html_result_resp.replace("{RESULT}", resp);
    request->send(200, "text/html", html_result_resp);
    sqlite3_finalize(res);
    digitalWrite ( led, 0 );
  } );

  server.onNotFound(notFound);
  server.begin();

  Serial.println ( "HTTP server started" );
}

void loop ( void ) {

}
